package local.xinshi.netmgr.pkg;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;


@Component
public class SqliteDao {
    private static final Logger logger = LoggerFactory.getLogger(SqliteDao.class);

    @Autowired
    DataSource ds;

    /**
     * 列出所有数据表
     * 
     * @return String 以","分隔所有表名的字符串,异常时返回空字符串
     */
    public String listTables() {
        StringBuilder sb = new StringBuilder();
        try (Connection conn = ds.getConnection();
                PreparedStatement st = conn.prepareStatement("select name from sqlite_master where type='table';");
                ResultSet rs = st.executeQuery()) {
            while (rs.next()) {
                sb.append(rs.getString(1) + ",");
            }
            if (sb.length() > 0)
                sb.deleteCharAt(sb.length() - 1);
        } catch (SQLException e) {
            logger.info(e.getMessage());
            return "";
        }
        return sb.toString().trim();
    }

    /**
     * 查询
     * 
     * @param sql  完整SQL语句,可带占位符
     * @param num 查询字段的数量，不可省略
     * @param cols 占位符的参数数组,没有占位符可传入null或省略(不可传入空字符串)
     * @return List存放行数据，元素是字符串数组(长度为num)，存放字段数据，没有记录时list.size()==0，异常时返回null
     */
    public List<String[]> query(String sql, int num, String... cols) {
        List<String[]> records = new ArrayList<>();
        try (Connection conn = ds.getConnection(); PreparedStatement st = conn.prepareStatement(sql)) {
            if (cols != null && cols.length != 0) {
                for (int i = 0; i < cols.length; i++) {
                    st.setObject(i + 1, cols[i]);
                }
            }
            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    String[] fields = new String[num];
                    for (int i = 0; i < num; i++) {
                        fields[i] = rs.getString(i + 1);
                    }
                    records.add(fields);
                }
            }
        } catch (SQLException e) {
            logger.info(e.getMessage());
            return null;
        }
        return records;
    }

    /**
     * 增删改
     * 
     * @param sql  完整SQL语句,可带占位符
     * @param args 占位符的参数数组,传入null或不传表示没有占位符
     * @return int 操作的行数,异常时返回 -1
     */
    public int execute(String sql, String... args) {
        try (Connection conn = ds.getConnection(); PreparedStatement st = conn.prepareStatement(sql);) {
            if (!(args == null || args.length == 0)) {
                for (int i = 0; i < args.length; i++) {
                    st.setObject(i + 1, args[i]);
                }
            }
            int i = st.executeUpdate();
            conn.commit();
            return i;
        } catch (SQLException e) {
            logger.info(e.getMessage());
            return -1;
        }
    }

    /**
     * 查询指定表名的所有字段信息
     * 
     * @param tb 数据表名称
     * @return String 以","分隔所有字段名的字符串,异常时返回空字符串
     */
    public String getCols(String tb) {
        StringBuilder sb = new StringBuilder();
        String sql = "pragma table_info(" + tb + ")";
        try (Connection conn = ds.getConnection();
                PreparedStatement st = conn.prepareStatement(sql);
                ResultSet rs = st.executeQuery()) {
            while (rs.next()) {
                sb.append(rs.getString(2) + ",");
            }
            if (sb.length() > 0)
                sb.deleteCharAt(sb.length() - 1);
        } catch (Exception e) {
            logger.info(e.getMessage());
            return "";
        }
        return sb.toString().trim();
    }

    /**
     * 检查指定表中是否存在指定记录
     * @param tb 表名
     * @param field 字段名
     * @param val 要检查的值
     * @return true(不存在)或false(已存在)
     * @deprecated 写了半天最后发现可能没什么用
     */
    @Deprecated
    public boolean notExists(String tb, String field, String val) {
        String sql = "select count() from " + tb + " where " + field + "=?";
        boolean b = true;
        try (Connection conn = ds.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, val);
            try (ResultSet set = ps.executeQuery()) {
                if (set.next())
                    b = set.getInt(1) < 0;
            }
            return b;
        } catch (Exception e) {
            logger.info(e.getMessage());
            return false;
        }
    }

}